package com.qfedu.mtl.dao.impl;

import com.qfedu.mtl.dao.ManagerDao;
import com.qfedu.mtl.entity.Managers;
import com.qfedu.mtl.entity.Menus;
import com.qfedu.mtl.query.managerQuery;
import com.qfedu.mtl.util.DBUtil;
import com.qfedu.mtl.util.handler.impl.BeanHandler;
import com.qfedu.mtl.util.handler.impl.BeanListHandler;
import com.qfedu.mtl.util.handler.impl.NumberHandler;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @version v1.0
 * @Date 2023/9/25 14:54
 * @Author DARKER
 * @Email 1402216045@qq.com
 */
public class ManagerDaoImpl implements ManagerDao {



    @Override
    public Managers selectByUsername(String username) {
        String sql = "select * from tb_managers where login_name=?";
        return DBUtil.executeDQL(sql,DBUtil.getConn(),new BeanHandler<>(Managers.class,true),username);
    }

    @Override
    public int insert(Managers dto) {
        String sql = "insert into tb_managers(login_name,login_pwd,mgr_name,mgr_gender," +
                "mgr_tel,mgr_email,mgr_qq,create_time) values(?,?,?,?,?,?,?,?)";
        return DBUtil.executeDML(sql,DBUtil.getConn(),dto.getLoginName(),dto.getLoginPwd(),dto.getMgrName(),dto.getMgrGender(),
                dto.getMgrTel(),dto.getMgrEmail(),dto.getMgrQq(),new Date());
    }

    @Override
    public int checkInfoRepeat(Managers dto) {
        String sql = "select count(*) from tb_managers where login_name=? or mgr_name=? or mgr_tel=? or mgr_email=? or mgr_qq=?";
        return DBUtil.executeDQL(sql,DBUtil.getConn(),new NumberHandler<>(Long.class),dto.getLoginName(),dto.getMgrName(),
                dto.getMgrTel(),dto.getMgrEmail(),dto.getMgrQq()).intValue();
    }

    @Override
    public List<Managers> pageByQuery(Integer page, Integer size, managerQuery query) {
        List<Object> values = new ArrayList<>();
        String sql="select m.*,r.role_name from tb_managers m left join tb_mgr_role mr on" +
                " m.mgr_id=mr.mgr_id left join tb_roles r on r.role_id=mr.role_id where 1=1 ";
        if (query.getLogin_name()!=null){
            sql += " and login_name like concat('%',?,'%')";
            values.add(query.getLogin_name());
        }
        if (query.getMgr_name()!=null){
            sql += " and mgr_name like concat('%',?,'%') ";
            values.add(query.getMgr_name());
        }
        //创建时间
//        if(query.getCreate_time()!=null){
//            sql += " and create_time<? ";
//            values.add(query.getCreate_time());
//        }
        if (query.getMgr_email()!=null){
            sql += " and mgr_email=?";
            values.add(query.getMgr_email());
        }

        if (query.getMgr_tel()!=null){
            sql += " and mgr_tel=? ";
            values.add(query.getMgr_tel());
        }

        if(query.getRole_name()!=null){
            sql += " and role_name=? ";
            values.add(query.getRole_name());
        }

        sql += " limit ?,?";
        values.add((page - 1) * size);
        values.add(size);
        return DBUtil.executeDQL(sql,DBUtil.getConn(),new BeanListHandler<>(Managers.class,true),values.toArray());
    }

    @Override
    public Integer selectCountQuery(managerQuery query) {
        List<Object> values = new ArrayList<>();
        String sql="select count(*) from tb_managers m left join tb_mgr_role mr on" +
                " m.mgr_id=mr.mgr_id left join tb_roles r on r.role_id=mr.role_id where 1=1 ";
        if (query.getLogin_name()!=null){
            sql += " and login_name like concat('%',?,'%')";
            values.add(query.getLogin_name());
        }
        if (query.getMgr_name()!=null){
            sql += " and mgr_name like concat('%',?,'%') ";
            values.add(query.getMgr_name());
        }

        if (query.getMgr_email()!=null){
            sql += " and mgr_email=? ";
            values.add(query.getMgr_email());
        }

        if (query.getMgr_tel()!=null){
            sql += " and mgr_tel=? ";
            values.add(query.getMgr_tel());
        }
//        //创建时间
//        if(query.getCreate_time()!=null){
//            sql += " and create_time<? ";
//            values.add(query.getCreate_time());
//        }
        if(query.getRole_name()!=null){
            sql += " and role_name=? ";
            values.add(query.getRole_name());
        }
        return DBUtil.executeDQL(sql,DBUtil.getConn(),new NumberHandler<>(Long.class),values.toArray()).intValue();


    }

    @Override
    public List<Menus> selectManagerAllMenus(Integer mgrId) {
        String sql= "select * from tb_menus where menu_id in(select menu_id from tb_role_menu where role_id in" +
                " (select role_id from tb_mgr_role where mgr_id=(select mgr_id from tb_managers where mgr_id=?)))";
        return DBUtil.executeDQL(sql,DBUtil.getConn(),new BeanListHandler<>(Menus.class,true),mgrId);
    }

    @Override
    public int update(Managers manager) {
        String sql = "update tb_managers set  login_name=?,login_pwd=?,mgr_name=?,mgr_gender=?,mgr_tel=?,mgr_email=?,mgr_qq=? where mgr_id=?";
        return DBUtil.executeDML(sql,manager.getLoginName(),manager.getLoginPwd(),manager.getMgrName(),manager.getMgrGender(),
                manager.getMgrTel(),manager.getMgrEmail(),manager.getMgrQq(),manager.getMgrId());
    }

    @Override
    public int deleteById(Integer id) {
        String sql = "delete from tb_managers where mgr_id=?";
        return DBUtil.executeDML(sql,id);
    }

    @Override
    public void deleteBatch(List<Integer> ids) {
        StringBuilder sqlBuilder = new StringBuilder("delete from tb_managers where mgr_id=? ");
        //批量拼接要删除的id
        for (int i = 0; i < ids.size()-1; i++) {
            sqlBuilder.append(" or mgr_id=? ");
        }

        DBUtil.executeDML(sqlBuilder.toString(),DBUtil.getConn(),ids.toArray());

    }

    @Override
    public void updateManagerRoleByManagerId(Integer mgrId, String roleName) {
        String sql = "update tb_mgr_role set role_id=(select role_id from tb_roles  where role_name=?) where mgr_id=?";
        DBUtil.executeDML(sql,DBUtil.getConn(),roleName,mgrId);
    }


    @Override
    public boolean selectExistMgrIdInTbRoles(Integer mgrId) {
        String sql = "select count(*) from tb_mgr_role where mgr_id=?";
        int row = DBUtil.executeDQL(sql, DBUtil.getConn(), new NumberHandler<>(Long.class), mgrId).intValue();
        if (row>0){
            return true;
        }
        return false;
    }

    @Override
    public void insertMgrBymgrId(Integer mgrId, String roleName) {
        String sql = "insert into  tb_mgr_role(role_id,mgr_id)   values((select role_id from tb_roles  where role_name=?),?)";
        DBUtil.executeDML(sql,DBUtil.getConn(),roleName,mgrId);
    }
}
